<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2012 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.8, 2012-10-12
 */

include("segurancasuper.php"); // Inclui o arquivo com o sistema de segurança
protegePagina(); // Chama a função que protege a página

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once 'excel/PHPExcel.php';


// Create new PHPExcel object
//echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Set document properties
//echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Tripe Criacao")
							 ->setLastModifiedBy("Tripe Criacao")
							 ->setTitle("Participantes que ja pagaram")
							 ->setSubject("Participantes que ja pagaram")
							 ->setDescription("Documento Excel gerado pelo sistema TRIPE de gerenciamento de participantes EMEJ")
							 ->setKeywords("office Excel php")
							 ->setCategory("export result file");


// Add some data
$i = 1;
//echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$i, 'ID')
            ->setCellValue('B'.$i, 'Nome')
            ->setCellValue('C'.$i, 'CPF')
            ->setCellValue('D'.$i, 'E-mail')
			->setCellValue('E'.$i, 'Empresa Jr')
            ->setCellValue('F'.$i, 'Federacao')
            ->setCellValue('G'.$i, 'Hospedagem')
            ->setCellValue('H'.$i, 'Sexo')
			->setCellValue('I'.$i, 'Instituicao de Ensino')
            ->setCellValue('J'.$i, 'Nascimento')
			->setCellValue('K'.$i, 'Tamanho da camisa')
			->setCellValue('L'.$i, 'Cidade')
			->setCellValue('M'.$i, 'Estado')
			->setCellValue('N'.$i, 'Necessidades especiais');
$i = $i + 1;

//$sql = "SELECT * FROM vw_inscritos";
//$sql = "SELECT * FROM (transacao join participantes) where ((participantes.id = transacao.idparticipante) and ((transacao.status = 3) or (transacao.status = 4)))";
//$sql = "select * from participantes JOIN (SELECT participantes.id as idpart FROM (transacao join participantes) where ((participantes.id = transacao.idparticipante) and ((transacao.status = 3) or (transacao.status = 4)))) AS T WHERE participantes.id <> T.idpart AND federada IS NOT NULL";
$sql = "select distinct participantes.* from participantes , (SELECT participantes.id as idpart FROM (transacao join participantes) where ((participantes.id = transacao.idparticipante) and ((transacao.status <> 3) AND (transacao.status <> 4)))) AS T WHERE participantes.id = T.idpart AND federada IS NOT NULL";
$query = mysql_query($sql);

while ($row = mysql_fetch_assoc($query))
{
	$data_array = explode("-",$row['nascimento']);
	$datanasc = $data_array[2].'/'.$data_array[1].'/'.$data_array[0];
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$i, $row['id'])
            ->setCellValue('B'.$i, $row['nome'])
            ->setCellValue('C'.$i, $row['cpf'])
			->setCellValue('D'.$i, $row['email'])
            ->setCellValue('E'.$i, $row['empresajr'])
			->setCellValue('F'.$i, $row['federacao'])
            ->setCellValue('G'.$i, $row['hospedagem'])
            ->setCellValue('H'.$i, $row['sexo'])
            ->setCellValue('I'.$i, $row['instensino'])
			->setCellValue('J'.$i, $datanasc)
            ->setCellValue('K'.$i, $row['tamanhocamisa'])
			->setCellValue('L'.$i, $row['cidade'])
			->setCellValue('M'.$i, $row['estado'])
			->setCellValue('N'.$i, $row['necespeciais']);
$i = $i + 1;
}

// Rename worksheet
//echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel5 file
//echo date('H:i:s') , " Write to Excel5 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;


// Echo memory peak usage
//echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
//echo date('H:i:s') , " Done writing files" , EOL;
//echo 'Files have been created in ' , getcwd() , EOL;

// We'll be outputting a Excel
header('Content-type: application/x-msexcel');

// It will be called downloaded.xls
header('Content-Disposition: attachment; filename="excel_etapa2.xls"');

// The Excel source is in original.xls
readfile('excel_etapa2.xls');
?>